package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Participation;

public class ParticipationDao {

	public ParticipationDao() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	String url = "jdbc:mysql://localhost:3306/QUESTUDY";
	String user = "root";
	String pass = "webclass";

	 //String pass = "webclass";

	// study 생성할 때 생성한 사람 study 가입
	public void insertStudyTeamLeader(int sid, int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("insert into participation(sid, uid, isConfirmed) values(?,?,1)");

			pstmt.setInt(1, sid);
			pstmt.setInt(2, uid);

			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 스터디 현재 가입한 사람
	public ArrayList<Participation> getSameStudyMember(int studyId)
			throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Participation> member = new ArrayList<Participation>();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select * from participation where sid=? and isConfirmed=1");

			pstmt.setInt(1, studyId);

			rs = pstmt.executeQuery();

			while (rs.next()) {
				int pid = rs.getInt("pid");
				int sid = rs.getInt("sid");
				int uid = rs.getInt("uid");
				int isConfirmed = rs.getInt("isConfirmed");
				member.add(new Participation(pid, sid, uid, isConfirmed));
			}

			return member;

		} catch (Exception ex) {
			ex.printStackTrace();
			return member;

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 스터디 현재 가입신청 한 사람
	public ArrayList<Participation> getSameStudyPotentialMember(int studyId)
			throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Participation> member = new ArrayList<Participation>();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select * from participation where sid=? and isConfirmed=0");

			pstmt.setInt(1, studyId);

			rs = pstmt.executeQuery();

			while (rs.next()) {
				int pid = rs.getInt("pid");
				int sid = rs.getInt("sid");
				int uid = rs.getInt("uid");
				int isConfirmed = rs.getInt("isConfirmed");
				member.add(new Participation(pid, sid, uid, isConfirmed));
			}

			return member;

		} catch (Exception ex) {
			ex.printStackTrace();
			return member;

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 스터디 신청한 경우
	public void insertParticipation(int sid, int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("insert into participation(sid, uid, isConfirmed) values(?,?,0)");

			pstmt.setInt(1, sid);
			pstmt.setInt(2, uid);

			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 스터디 신청 수락
	public void updateParticipation(int sid, int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("update participation set isConfirmed=1 where sid=? and uid=?");
			pstmt.setInt(1, sid);
			pstmt.setInt(2, uid);
			pstmt.executeUpdate();

			pstmt = con
					.prepareStatement("select deposit from study, deposit where depositId=did and studyid=?");
			pstmt.setInt(1, sid);
			ResultSet rs = pstmt.executeQuery();;
			int deposit = 0;
			while (rs.next()) {
				deposit = rs.getInt("deposit");
			}
			
			pstmt = con
					.prepareStatement("update study set currentMember=currentMember+1, "
							+ "currentDeposit=currentDeposit+? where studyid=?");
			pstmt.setInt(1, deposit);
			pstmt.setInt(2, sid);
			pstmt.executeUpdate();
;
			
			StudyFineDao dao = new StudyFineDao();
			dao.insertStudyFineInfo(sid, uid);

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 스터디 신청 거절
	public void deleteParticipation(int sid, int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("delete from participation where sid=? and uid=?");

			pstmt.setInt(1, sid);
			pstmt.setInt(2, uid);

			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}
}